from tkinter import *
from tkinter import filedialog
from tkinter import ttk
from threading import Thread
from queue import Queue
from pyppeteer import launch
from pyppeteer.browser import Browser
from pyppeteer.network_manager import Response
from mini_tools.cjjtool import *
from mini_tools.myexcel import *
from mini_tools.mytools import *
from ftplib import FTP
from mini_tools.ftpLoad import downloadImg
from sql import sqlseverDB
import tkinter.messagebox as messagebox
import openpyxl,re,asyncio,concurrent.futures


class HomeFrame(Frame):
    def __init__(self,master=None):
        Frame.__init__(self,master)
        Label(self, text='采集区', fg="black", bg="yellow",font=("微软雅黑", 13)).pack(fill=X,padx=2)
        ZjljcjFrame(self).pack(pady=5,fill=X,padx=5)
        ShuaDanFrame(self).pack(pady=5,fill=X,padx=5)
        CJSkuFrame(self).pack(pady=5,fill=X,padx=5)


class ZjljcjFrame(Frame):
    def __init__(self, master=None):
        Frame.__init__(self, master)
        # self.pack(fill=X,padx=3,pady=3)
        self.statustxt = StringVar()    
        self.statustxt.set(f'当前未导入表格')
        self.statustxt2 = StringVar()    
        self.statustxt2.set(f'当前未导入表格')

        self.createWidgets()

    def createWidgets(self):

        ttk.Separator(self, orient='horizontal').pack(fill=X, pady=5)

        f0 = Frame(self)
        f0.pack(fill=X, padx=2, pady=2)
        # Label(f0, text='采集自建链接', fg="#8E236B", bg="#3299CC",font=("微软雅黑", 14)).grid(row=1,column=1,pady=5,columnspan=3)
        f00 = Frame(f0)
        f00.grid(row=2, column=1, sticky='nw', padx=5)
        Label(f00, text='店铺网址:').pack(side=LEFT, padx=2, pady=2)
        self.searchDpIpt = Entry(f00)
        self.searchDpIpt.pack(side=LEFT, padx=2, pady=2)
        Button(f00, text='选择文件', command=self.choosefile2).pack(side=LEFT, padx=2, pady=2)
        Label(f00, textvariable=self.statustxt2,fg='red').pack(side=LEFT, padx=2, pady=2)
        
        f01 = Frame(f0)
        f01.grid(row=3, column=1, sticky='nw', padx=5)
        Label(f01, text='导入excel:').pack(side=LEFT, padx=2, pady=2)
        self.DaoRuIpt = Entry(f01)
        self.DaoRuIpt.pack(side=LEFT, padx=2, pady=2)
        Button(f01, text='选择文件', command=self.choosefile).pack(side=LEFT, padx=2, pady=2)
        Label(f01, textvariable=self.statustxt,fg='red').pack(side=LEFT, padx=2, pady=2)
        Button(f0, text='采集自建链接', command=self.cjzj, fg='#ffffff', bg='green',width=20).grid(row=4, column=1, sticky='nw', padx=10, pady=5)

    def choosefile(self):

        select_path = filedialog.askopenfilename()
        if select_path.endswith('.xlsx'):
            self.DaoRuIpt.delete(0, END)
            self.DaoRuIpt.insert(0, select_path)
            self.fpath = select_path
            wb = openpyxl.load_workbook(select_path)
            ws = wb[wb.sheetnames[0]]
            max_row = ws.max_row
            self.statustxt.set(f'当前表格共{max_row}行')
        else:
            messagebox.showerror('错误提示', '请选择excel文件')
    
    def choosefile2(self):

        select_path = filedialog.askopenfilename()
        if select_path.endswith('.xlsx'):
            self.searchDpIpt.delete(0, END)
            self.searchDpIpt.insert(0, select_path)
            self.fpath2 = select_path
            wb = openpyxl.load_workbook(select_path)
            ws = wb[wb.sheetnames[0]]
            max_row = ws.max_row
            self.statustxt2.set(f'当前店铺共{max_row-1}行')
        else:
            messagebox.showerror('错误提示', '请选择excel文件')

    def cjzj(self):

        if not self.searchDpIpt.get().strip():
            messagebox.showinfo('提示', '请导入店铺表格')
            return

        if not self.DaoRuIpt.get().strip():
            messagebox.showinfo('提示', '请先导入表格')

        Thread(target=self.cjzj0).start()

    def cjzj0(self):

        self.new_datas = []
        self.ycjp = []
        self.ycjids = []
        asyncio.run(self.begin_cj())

    async def begin_cj(self):

        objcig = get_config()
        bargs = ['--disable-infobars', '--start-maximized']
        if objcig['proxy']:
            bargs.append(f"--proxy-server={objcig['proxy']}")
        kchrome()
        browser = await launch(
            handleSIGINT=False,
            handleSIGTERM=False,
            handleSIGHUP=False,
            headless=objcig['headless'],
            # headless=True,
            userDataDir=objcig['userDataDir'],
            executablePath=objcig['executablePath'],
            args=bargs,
            defaultViewport=None
        )
        self.queue = asyncio.Queue()
        dpdatas= getDictDatasByEecel(delempty(self.fpath2))
        
        self.dpcount=len(dpdatas)
        for dpdata in dpdatas:
            await self.queue.put([dpdata['DianPuWangZhi'],0])

        tasks = [asyncio.create_task(self.cj_purl(browser)) for j in range(1)]
        await asyncio.gather(*tasks)
        print(f'采集完毕,共有{len(self.new_datas)}个产品,开始更新到表格...')
        self.upecl()

        print('店铺链接更新完毕')
        messagebox.showinfo('提示', '店铺链接更新完毕')
        await asyncio.sleep(3)
        await browser.close()

    def upecl(self):
        pros = self.new_datas
        # with open('lll.json','w',encoding='utf-8') as f:
        #     json.dump(pros,f)
        delempty(self.fpath)
        wb = openpyxl.load_workbook(self.fpath)
        ws = wb[wb.sheetnames[0]]
        titles = [getpy(ws.cell(1, col).value) for col in range(1, ws.max_column+1)]
        datas = getDictDatasByEecel(ws)

        curys = {}

        proks=[]
        curshopid=[]
        for pro in pros:
            shopid = pro['shopid']
            if str(shopid) not in curshopid:
                curshopid.append(str(shopid))
            itemid = pro['itemid']
            pkey = (str(shopid), str(itemid))
            proks.append(pkey)
            curys[pkey]=pro

        xiajiacount=0
        upcount = 0
        haskeys=[]
        crr=2
        for item in datas:
            #忽略不同店铺
            ckey = get_pid_byurl(item['ChanPinLianJie'])
            if ckey[0] not in curshopid:
                print(f'第{crr}行,非本店铺产品忽略')
                crr+=1
                continue
            #忽略是否更新链接为不1的
            isGenXin=str(item['ShiFouGengXinLianJie'])
            if isGenXin!='1':
                print(f'第{crr}行,更新列值不为 1 忽略')
                crr+=1
                continue
            ssl=item['SouSuoLiang']
            ssl=0 if not ssl else int(item['SouSuoLiang'])
            if ckey not in proks:
                #下架产品点击数变0
                ws.cell(crr,titles.index('DianJiCiShu')+1).value=0
                ws.cell(crr,titles.index('ZongXiaoLiang')+1).value=0
                xiajiacount+=1
                print(f'下架数据,第{crr}行数据下架')
            else:
                #未下架产品更新
                cpro=curys[ckey]
                ws.cell(crr, titles.index('PingFen')+1).value = round(cpro['item_rating']['rating_star'], 1)
                ws.cell(crr, titles.index('PingJiaShuLiang') +1).value = cpro['cmt_count']
                ws.cell(crr, titles.index('ZongXiaoLiang') +1).value = cpro['historical_sold']
                ws.cell(crr,titles.index('YueXiaoLiang')+1).value=cpro['sold']
                #ws.cell(crr, titles.index('ChanPinLianJie')+1).value = f'https://shopee.co.id/pro-i.{shopid}.{itemid}'
                ws.cell(crr, titles.index('BiaoTi') +1).value = cpro['name']
                ws.cell(crr, titles.index('ZuiDiJia') +1).value = cpro['price_min']/100000
                if cpro['historical_sold']>0 and ssl>300:
                    ws.cell(crr,titles.index('DianJiCiShu')+1).value=1
                else:
                    ws.cell(crr,titles.index('DianJiCiShu')+1).value=0
                print(f'更新数据,第{crr}行数据更新')
                if ckey not in haskeys:
                    haskeys.append(ckey)
                upcount += 1
            crr+=1
        
        insertcount = 0
        cur_maxrow = ws.max_row+1
        #新增数据
        for k,pro in curys.items():
            if k not in haskeys:
                shopid=pro['shopid']
                itemid=pro['itemid']
                cr = cur_maxrow
                ws.cell(cr, titles.index('PingFen') +1).value = round(pro['item_rating']['rating_star'], 1)
                ws.cell(cr, titles.index('PingJiaShuLiang') +1).value = pro['cmt_count']
                ws.cell(cr, titles.index('ZongXiaoLiang') +1).value = pro['historical_sold']
                ws.cell(cr,titles.index('YueXiaoLiang')+1).value=pro['sold']
                ws.cell(cr, titles.index('ChanPinLianJie') +1).value = f'https://shopee.co.id/pro-i.{shopid}.{itemid}'
                ws.cell(cr, titles.index('BiaoTi')+1).value = pro['name']
                ws.cell(cr, titles.index('ZuiDiJia') +1).value = pro['price_min']/100000
                if pro['historical_sold']==0:
                    ws.cell(cr,titles.index('DianJiCiShu')+1).value=0
                else:
                    ws.cell(cr,titles.index('DianJiCiShu')+1).value=1
                print(f'新增数据,当前总行数{cur_maxrow}')
                cur_maxrow += 1
                insertcount += 1
        
        print(f'本次检测下架行数:{xiajiacount}')
        print(f'本次检测更新行数:{upcount}')
        print(f'本次检测新增行数:{insertcount}')


        wb.save(self.fpath)

    async def intercept_response(self, res: Response, p,dpurl):

        req = res._request
        cur_url = req.url
        if 'shopee.co.id/api/v4/shop/rcmd_items' in cur_url:
            try:
                if (dpurl,p) in self.ycjp:
                    return
                self.ycjp.append((dpurl,p))
                json_data = await res.json()
                pros = json_data['data']['items']
                curpros = []
                for pro in pros:
                    if pro['itemid'] not in self.ycjids:
                        self.new_datas.append(pro)
                        self.ycjids.append(pro['itemid'])
                        curpros.append(pro)
                print(f'第{p+1}页,接口采集完毕,采集到{len(curpros)}个')

            except Exception as e:
                print(f'第{p+1}页,接口错误 => {e}')

    async def cj_purl(self, browser: Browser):
        page = (await browser.pages())[0]

        yjsdp=0
        while True:
            err_cont = 0
            try:
                print(f'当前任务队列:{self.queue.qsize()}')
                item = await self.queue.get()
                if item is None:
                    break
                dpurl,p = item
                page.on('response', lambda rep: asyncio.ensure_future(self.intercept_response(rep, p,dpurl)))

                purl = add_query_parameters(dpurl, {'page': p})
                await repeat_goto(page, purl)
                await page.waitFor(2000)
                while True:
                    noexceit = await page.xpath('//div[@role="main" and @class="product-not-exist"]')
                    elempty = await page.xpath('//div[@role="main"]//div[@class="shopee-search-empty-result-section"]')
                    dlel = await page.xpath('HRbynn')
                    oo = await page.xpath('//div[@class="shop-page__all-products-section"]//a[@data-sqe="link"]')
                    oo2 = await page.xpath('//div[@class="shop-search-result-view"]//a[@class="contents"]')
                    if noexceit or elempty or dlel:
                        print(f'产品不存在')
                        break
                    if oo or oo2:
                        await page.waitFor(2000)
                        break
                    await page.waitFor(1000)

                if p == 0:
                    cot = await page.content()
                    elmaxp = etree.HTML(cot).xpath('//span[@class="shopee-mini-page-controller__total"]/text()')
                    if elmaxp:
                        maxp = int(elmaxp[0].strip())
                        print(f'获取到当前店铺最大页数为{maxp}')
                    else:
                        maxp = 9
                    
                    for ppp in range(1, maxp):
                        await self.queue.put([dpurl,ppp])
                    yjsdp+=1
                    if yjsdp==self.dpcount:

                        for _ in range(10):
                            await self.queue.put(None)

            except Exception as e:
                print(f'第{p+1}页-出错 => {e}')
                err_cont += 1
                if err_cont > 10:
                    print('出错超过10次,结束本次采集')
                    break

        await asyncio.sleep(1)
        await page.close()

class ShuaDanFrame(Frame):
    def __init__(self, master=None):
        Frame.__init__(self, master)
        # self.pack(fill=X,padx=3,pady=3)
        self.statustxt = StringVar()    
        self.statustxt.set(f'当前未导入表格')
        self.createWidgets()

    def createWidgets(self):

        ttk.Separator(self, orient='horizontal').pack(fill=X, pady=5)

        f0 = Frame(self)
        f0.pack(fill=X, padx=2, pady=2)
        # Label(f0, text='采集自建链接', fg="#8E236B", bg="#3299CC",font=("微软雅黑", 14)).grid(row=1,column=1,pady=5,columnspan=3)

        f01 = Frame(f0)
        f01.grid(row=1, column=1, sticky='nw', padx=5)

        Label(f01, text='导入excel:').pack(side=LEFT, padx=2, pady=2)
        self.DaoRuIpt = Entry(f01)
        self.DaoRuIpt.pack(side=LEFT, padx=2, pady=2)
        Button(f01, text='选择文件', command=self.choosefile).pack(
            side=LEFT, padx=2, pady=2)
        Label(f01, textvariable=self.statustxt,fg='red').pack(side=LEFT, padx=2, pady=2)
        

        f02 = Frame(f0)
        f02.grid(row=2, column=1, sticky='nw', padx=5)

        Label(f02, text='必须含图片:').pack(side=LEFT, padx=10, pady=2)
        self.ispicSelect = ttk.Combobox(f02,width=5,state='readonly')
        self.ispicSelect['value']=['是','否']
        self.ispicSelect.pack(side=LEFT, padx=2, pady=2)
        self.ispicSelect.current(0)

        Button(f0, text='生成刷单文件', command=self.create_shudan, fg='#ffffff', bg='green',
               width=20).grid(row=3, column=1, sticky='nw', padx=10, pady=5)

    def choosefile(self):

        select_path = filedialog.askopenfilename()
        if select_path.endswith('.xlsx'):
            self.DaoRuIpt.delete(0, END)
            self.DaoRuIpt.insert(0, select_path)
            self.fpath = select_path
            wb = openpyxl.load_workbook(select_path)
            ws = wb[wb.sheetnames[0]]
            max_row = ws.max_row
            self.statustxt.set(f'当前表格共{max_row}行')
        else:
            messagebox.showerror('错误提示', '请选择excel文件')

    def create_shudan(self):

        if not self.DaoRuIpt.get().strip():
            messagebox.showinfo('提示', '请先导入表格')

        Thread(target=self.create_shudan0).start()

    def create_shudan0(self):
        
        self.ms=sqlseverDB('{SQL Server}', '199.33.126.84,1433', 'shopeecoid', 'sa', 'Y3GwLHaP8zQ0seZG')
        from ftplib import FTP


        self.curdir=os.path.dirname(self.fpath)
        wb=openpyxl.load_workbook(self.fpath)
        delempty(self.fpath)
        self.ws=wb[wb.sheetnames[0]]
        datas=getDictDatasByEecel(self.ws)
        self.tits=[getpy(self.ws.cell(1,col).value) for col in range(1,self.ws.max_column+1)]
        self.ztask=[]
        self.imgtask=Queue(-1)
        self.imgstatus=self.ispicSelect.current()



        row=2
        for data in datas:
            falgzj,taurl=self.getdbdata([row,data])
            if falgzj==0:
                print('错误提示',f'第{row}行,自建链接《{taurl}》评论不足,退出本次刷单打包')
                messagebox.showerror('错误提示',f'第{row}行,自建链接《{taurl}》评论不足')
                return
            row+=1
        wb.save(self.fpath)
        print('开始下载图片...')
        xcs=[]
        for j in range(5):
            t0=Thread(target=self.reqimg)
            t0.start()
            xcs.append(t0)

        for xc in xcs:
            xc.join()
        
        
        print('所有图片下载完毕,开始生成刷单文件,请等待打包...')

        zipname=os.path.basename(self.fpath).replace('.xlsx','')
        print(self.curdir,zipname)
        self.zip_directory(self.curdir,zipname)

    def zip_directory(self,source_dir, zip_filename):
        import shutil
        try:
            # 执行打包操作，保存在 destination_dir 中
            destination_dir=os.path.dirname(source_dir)
            zip_file_path = os.path.join(destination_dir, zip_filename)
            shutil.make_archive(zip_file_path, 'zip', source_dir)
            print(f"成功创建 ZIP 归档文件: {zip_filename}.zip")
            shutil.move(f'{zip_file_path}.zip', source_dir)
        except Exception as e:
            print(f"创建 ZIP 归档文件时出错: {e}")


    def getdbdata(self,item):
        try:
            row,data=item
            tarenUrl=data['ChanPinLianJie']
            if not get_pid_byurl(tarenUrl):
                print(f'【EXCEL日志】第{row}行,链接格式错误')
                return
            shopid,itemid=get_pid_byurl(tarenUrl)
            zjpid=f'{shopid}-{itemid}'

            if self.imgstatus:
                sqlstr='''select top 1 [id],[PingYu],[picdir] from [comment] where [YiShiYong]=0 and [PingLunShenHeZhuangTai]=1 and [isDwonloadImg]!=0 and [zjpid]=?'''
            else:
                sqlstr='''select top 1 [id],[PingYu],[picdir] from [comment] where [YiShiYong]=0 and [PingLunShenHeZhuangTai]=1 and [isDwonloadImg]=1 and [zjpid]=?'''
            rs=self.ms.ExecQuery(sqlstr,(zjpid,))
            if len(rs)==0:
                print(f'【数据库日志】第{row}行,数据库未找到《{zjpid}》相关数据')
                return (0,tarenUrl)

            zzid,pingyu,picdir=rs[0]
            aff=self.ms.ExecNoQuery('update [comment] set [YiShiYong]=1 where [id]=?',(zzid,))
            print(f'【数据库日志】自增id:{zzid} 更改使用标记转态 {"成功" if aff else "失败"},图片任务剩余:{self.imgtask.qsize()}')

            #填写Excel表 评价内容 和 评论文件夹 两列
            self.ws.cell(row,self.tits.index('PingJiaNeiRong')+1).value=pingyu
            self.ws.cell(row,self.tits.index('PingJiaShiPinHuoTuPianWenJianJiaMingCheng')+1).value=picdir

            #将图片下载任务加入线程队列
            self.imgtask.put(picdir)

            return (1,tarenUrl)

        except Exception as e:
            print(e)
            return (0,tarenUrl)
    
    def reqimg(self):
        ftp = FTP()
        ftp.connect('199.33.126.84', 66)
        ftp.login('administrator', 'YUchen1021')
        while True:
            try:
                if self.imgtask.empty():
                    print('【图片日志】所有图片下载完毕')
                    break
                picdir=self.imgtask.get()
                downloadImg(ftp,picdir,f'{self.curdir}/{picdir}')
            except:
                downloadImg(ftp,picdir,f'{self.curdir}/{picdir}')

class CJSkuFrame(Frame):
    def __init__(self,master=None):
        Frame.__init__(self,master)
        self.ms = sqlseverDB('{SQL Server}', SQL_SERVER, 'SuMaiTongPol', SQL_USER, SQL_PWD)
        self.statusStr=StringVar()
        self.statusStr.set('请先输入商家编码查询任务状态')
        self.t0=Thread(target=self.getcount)
        self.createWidgets()

    def createWidgets(self):
        f0=Frame(self)
        f0.pack(fill=X,padx=2,pady=2)
        Label(f0, text='采集店铺库存', fg="white", bg="#007FFF",font=("微软雅黑", 13)).pack(fill=X,padx=5)

        f1=Frame(self)
        f1.pack(fill=X,padx=2,pady=2)
        f00=Frame(f1)
        f00.pack(fill=X,side=LEFT,padx=2,pady=2)
        f000=Frame(f00)
        f000.pack(fill=X)
        Label(f000, text='商家编码:').pack(side=LEFT, padx=2, pady=2)
        self.SJBMIpt = Entry(f000,width=10)
        self.SJBMIpt.pack(side=LEFT,padx=2, pady=2)
        self.btn_catstatus=Button(f000,text='查询任务状态',command=lambda:Thread(target=self.cat_skutask).start(),fg='#ffffff',bg='green')
        self.btn_catstatus.pack(side=LEFT, padx=2, pady=2)
        self.btn_cjsku=Button(f000,text='发布采集任务',command=lambda:Thread(target=self.send_task).start(),fg='#ffffff',bg='green',state=DISABLED)
        self.btn_cjsku.pack(side=LEFT, padx=2, pady=2)
        Label(f000, textvariable=self.statusStr,fg='red').pack(side=LEFT, padx=2, pady=2)

        f2=Frame(self)
        f2.pack(fill=X)
        ALiUrlFrame(f2).pack(side=LEFT,fill=X)
    
    def getcount(self):
        while True:
            try:
                rs1=self.ms.ExecQuery('select count(*) from adtask where sjbm=? and state=0',(self.sjbm,))
                if rs1[0][0]==0:
                    self.btn_cjsku.config(state=NORMAL)
                    self.statusStr.set(f'商家编码:《{self.sjbm}》,采集SKU任务未开启')

                else:
                    self.btn_cjsku.config(state=DISABLED)
                    self.statusStr.set(f'商家编码:《{self.sjbm}》任务执行中,剩余页面:{rs1[0][0]}')
                
                time.sleep(5)
            except Exception as e:
                pass
    
    def cat_skutask(self):

        sjbm=self.SJBMIpt.get().strip()
        if not sjbm:
            messagebox.showinfo('提示','请先输入商家编码')
            return
        self.btn_catstatus.config(state=DISABLED)
        self.sjbm=sjbm
        #先查询商家编码是否合法
        print('正在校准商家码...')
        self.statusStr.set('正在校准商家码...')
        rs=trydosql(self.ms,'select count(*) from adpro where sjbm=?',(sjbm,))
        if rs[0][0]==0:
            print(f'无效商家编码:{sjbm}')
            self.statusStr.set(f'无效商家编码:{sjbm}')
            messagebox.showinfo('提示',f'无效商家编码:{sjbm}')
            self.btn_catstatus.config(state=NORMAL)
            return

        #查询该商家编码是否在任务中
        print('开始查询是否商家在任务中...')
        rs=self.ms.ExecQuery('select count(*) from adtask where sjbm=? and state=0',(sjbm,))
        if rs[0][0]>0:
            print(f'商家编码:《{sjbm}》,正在执行采集任务,任务剩余页面:{rs[0][0]}')

        else:

            print(f'商家编码:《{sjbm}》,采集任务未开启,可发布任务')
            self.btn_cjsku.config(state=NORMAL)

        if not self.t0.is_alive():
            self.t0.start()
            print(f'检测开启')
    
        
        self.btn_catstatus.config(state=NORMAL)

    def send_task(self):
        if not messagebox.askyesno('提示','确定发布采集SKU任务'):
            return
        self.btn_cjsku.config(state=DISABLED)
        sqlstr='''select shopid,ProductID,dpurl,sjbm,sjname,yyname,ProductNameAdName from adpro 
                    where id in(select min(id) from adpro where sjbm=? group by productid)'''
        
        rs=self.ms.ExecQuerydict(sqlstr,(self.sjbm))
        print(f'待发布任务:{len(rs)}')
        self.send_count=len(rs)
        sufb=0
        sbfb=0
        with concurrent.futures.ThreadPoolExecutor(max_workers=5) as executor:
            futures=[]
            row=1
            for r in rs:

                futures.append(executor.submit(self.upload_task,row,r))
                row+=1
            for future in concurrent.futures.as_completed(futures):
                try:
                    res_status=future.result()
                    if res_status:
                        sufb+=1
                    else:
                        sbfb+=1
                except Exception as exc:
                    print(f"线程出错: {exc}")
        
        print(f'发布完成,共{self.send_count}个任务,其中成功发布:{sufb}个,失败{sbfb}个')
        messagebox.showinfo('提示',f'发布完成,共{len(rs)}个任务,其中成功发布:{sufb}个,失败{sbfb}个')

    def upload_task(self,row,pitem):
        pid=pitem['ProductID']
        self.send_count-=1
        rs=self.ms.ExecQuery('select count(*) from adtask where ProductID=?',(pid,))
        if rs[0][0]>0:
            aff=self.ms.ExecNoQuery('update adtask set state=0 where ProductID=?',(pid,))

            if aff>0:
                print(f'第{row}个,pid:{pid},已存在,将state状态变0成功,发布成功,剩余{self.send_count}个未发布')
                return 1
            else:
                print(f'第{row}个,pid:{pid},已存在,将state状态变0失败,发布失败,剩余{self.send_count}个未发布')
                return 0
        else:
            params=list(pitem.values())
            zdstr=','.join([k for k in pitem.keys()])
            wstr=','.join(['?' for _ in params])

            aff=self.ms.ExecNoQuery(f'insert into adtask({zdstr}) values ({wstr})',params)
            if aff>0:
                print(f'第{row}个,pid:{pid},不存在,新增成功,发布成功,剩余{self.send_count}个未发布')
                return 1
            else:
                print(f'第{row}个,pid:{pid},不存在,新增失败,发布失败,剩余{self.send_count}个未发布')
                return 0


class ALiUrlFrame(Frame):
    def __init__(self,master=None):
        Frame.__init__(self,master)
        self.ms = sqlseverDB('{SQL Server}', SQL_SERVER, 'SuMaiTongPol', SQL_USER, SQL_PWD)
        self.hyCount=IntVar()
        self.SkuCount=IntVar()
        self.rowCount=IntVar()
        self.createWidgets()

    def createWidgets(self):

        f1=Frame(self)
        f1.pack(fill=X,padx=2,pady=2)
        f00=Frame(f1)
        f00.pack(fill=X)

        Label(f00, text='商家编码:').pack(side=LEFT, padx=2, pady=2)
        self.SJBMIpt = Entry(f00,width=10)
        self.SJBMIpt.pack(side=LEFT,padx=2, pady=2)
        #Thread(target=self.getcount).start()
        self.btn_dcAliurl=Button(f00,text='导出1688货源链接',command=lambda:Thread(target=self.dc_aliurl).start(),fg='#ffffff',bg='green')
        self.btn_dcAliurl.pack(side=LEFT, padx=2, pady=2)
        Label(f00, textvariable=self.hyCount,fg='red').pack(side=LEFT, padx=2, pady=2)
        self.btn_dcSku=Button(f00,text='导出印尼库存表',command=lambda:Thread(target=self.dc_sku).start(),fg='#ffffff',bg='green')
        self.btn_dcSku.pack(side=LEFT, padx=2, pady=2)
        Label(f00, textvariable=self.SkuCount,fg='red').pack(side=LEFT, padx=2, pady=2)

        Label(f00, text='库存表路径:').pack(side=LEFT, padx=2, pady=2)
        self.DaoRuIpt = Entry(f00)
        self.DaoRuIpt.pack(side=LEFT, padx=2, pady=2)
        Label(f00, textvariable=self.rowCount,fg='red')
        Button(f00, text='选择文件', command=self.choosefile).pack(side=LEFT, padx=2, pady=2)
        self.btn_drSku=Button(f00,text='上传印尼库存表',command=lambda:Thread(target=self.up_sku).start(),fg='#ffffff',bg='green')
        self.btn_drSku.pack(side=LEFT, padx=2, pady=2)
    
    def getcount(self):
        while True:
            if self.SJBMIpt.get().strip():
                self.sjbm=self.SJBMIpt.get().strip()
                try:
                    rs1=self.ms.ExecQuery('''select COUNT(DISTINCT aliurl) from ProShopeePh where  SJBM=? and aliurl is not null  and aliurl  
                                            not in(select aliurl from YNSkuStock where aliurl is not null and SJBM=? )  
                                        ''',(self.sjbm,self.sjbm,))
                    rs2=self.ms.ExecQuery('select count(DISTINCT purl) from ynskustock where sjbm=?',(self.sjbm,))
                    self.hyCount.set(rs1[0][0])
                    self.SkuCount.set(rs2[0][0])
                    
                    
                except Exception as e:
                    pass
            
                time.sleep(5)
     
    def choosefile(self):

        select_path = filedialog.askopenfilename()
        if select_path.endswith('.xlsx'):
            self.DaoRuIpt.delete(0, END)
            self.DaoRuIpt.insert(0, select_path)
            self.fpath = select_path
            wb = openpyxl.load_workbook(select_path)
            ws = wb[wb.sheetnames[0]]
            max_row = ws.max_row
            self.rowCount.set(max_row-1)
        else:
            messagebox.showerror('错误提示', '请选择excel文件')
    
    def dc_aliurl(self):

        sjbm=self.SJBMIpt.get().strip()
        if not sjbm:
            messagebox.showinfo('提示','请先输入商家编码')
            return
        self.btn_dcAliurl.config(state=DISABLED)
        sqlstr='''select aliurl [1688货源链接],yntitle [印尼标题] from ProShopeePh
                    where id in( select min(id) from ProShopeePh
                    where  SJBM=? 
                    and aliurl is not null  
                    and aliurl not in(select aliurl from YNSkuStock where aliurl is not null and SJBM=?)
                    group by aliurl)'''

        fqz=f'{sjbm}_1688货源链接'
        fname=sqlToExcel(fqz,self.ms,sqlstr,(sjbm,sjbm))

        if fname:
            print(f'成功导出:{fname}')
            messagebox.showinfo('提示',f'成功导出:{fname}')
        else:
            print(f'无效导出:{sjbm}')
            messagebox.showinfo('提示',f'无效导出:{sjbm}')
        self.btn_dcAliurl.config(state=NORMAL)
    
    def dc_sku(self):

        sjbm=self.SJBMIpt.get().strip()
        if not sjbm:
            messagebox.showinfo('提示','请先输入商家编码')
            return
        self.btn_dcSku.config(state=DISABLED)
        sqlstr='''
                    select sjbm,dpurl,purl,ProductNameAdName,saleprice,aliurl from ynskustock
                    where [id] in (select min(id) from ynskustock where sjbm=? group by purl) order by aliurl desc
                '''

        fqz=f'{sjbm}_印尼库存表'
        fname=sqlToExcel(fqz,self.ms,sqlstr,(sjbm,))

        if fname:
            print(f'成功导出:{fname}')
            messagebox.showinfo('提示',f'成功导出:{fname}')
        else:
            print(f'无效导出:{sjbm}')
            messagebox.showinfo('提示',f'无效导出:{sjbm}')
        self.btn_dcSku.config(state=NORMAL)

    def up_sku(self):

        def upsku(crow,pitem:dict):
            cpurl=pitem.pop('purl')
            params=list(pitem.values())
            zdstr=','.join([f'{k}=?' for k in pitem.keys()])
            wstr=','.join(['?' for _ in params])

            sqlstr=f'''update ynskustock set {zdstr} where purl=?'''

            aff=self.ms.ExecNoQuery(sqlstr,params+[cpurl])

            return crow,cpurl,aff

        if not self.DaoRuIpt.get().strip():
            messagebox.showinfo('提示','请先导入文件')
            return
        
        self.btn_drSku.config(state=DISABLED)
        pdatas=getDictDatasByEecel2(self.fpath)
        with concurrent.futures.ThreadPoolExecutor(max_workers=5) as executor:
            futures=[]
            row=2
            for pdata in pdatas:
                futures.append(executor.submit(upsku,row,pdata))
                row+=1

            for future in concurrent.futures.as_completed(futures):
                try:
                    res_row,res_purl,res_aff = future.result()
                    print(f'第 {res_row} 行,purl:{res_purl},更新sku数:{res_aff}')
                except Exception as exc:
                    print(f"线程出错: {exc}")
        print(f'全部上传完毕')
        messagebox.showinfo('提示','全部上传完毕')
        self.btn_drSku.config(state=NORMAL)
    
# import tkinter
# list_win = tkinter.Tk()
# list_win.wm_geometry("800x650")
# list_win.title('主页')
# HomeFrame(list_win).pack(fill=X, padx=3, pady=3)
# list_win.mainloop()